﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;

using DbHelper;//自定义帮助类
using Gx.Model.Entity;//实体
using Gx.Common;//通用类
namespace Gx.Dal.Employee
{
    /// <summary>
    /// 部门数据层
    /// </summary>
    public class DepartmentInfoDal : SqlServerHelper, IDisposable
    {
        public bool AddDepartmentInfo(E_DepartmentInfo model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("INSERT INTO E_DepartmentInfo");
            strSql.Append("(");
            strSql.Append("DepName,");
            strSql.Append("ParentId,");
            strSql.Append("Status");
            strSql.Append(")");
            strSql.Append("VALUES");
            strSql.Append("(");
            strSql.Append("@DepName,");
            strSql.Append("@ParentId,");
            strSql.Append("@Status");
            strSql.Append(")");
            List<SqlParameter> pList = new List<SqlParameter>();
            pList.Add(new SqlParameter("@DepName", SqlDbType.NVarChar, 60) { Value = model.DepName });
            pList.Add(new SqlParameter("@ParentId", SqlDbType.Int, 4) { Value = model.ParentId });
            pList.Add(new SqlParameter("@Status", SqlDbType.TinyInt, 1) { Value = model.Status });
            return ExecuteNonQuery(ConfigHelper.ConnectionStrings, strSql.ToString(), pList.ToArray()) > 0;
        }

        public bool DelDepartmentInfo(int Id)
        {
            string strSql = "UPDATE E_DepartmentInfo SET Status=1 WHERE Id=@Id";
            List<SqlParameter> pList = new List<SqlParameter>();
            pList.Add(new SqlParameter("@Id", SqlDbType.Int, 4) { Value = Id });
            return ExecuteNonQuery(ConfigHelper.ConnectionStrings, strSql.ToString(), pList.ToArray()) > 0;
        }

        public bool EditDepartmentInfo(E_DepartmentInfo model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("UPDATE E_DepartmentInfo SET ");
            strSql.Append(" DepName=@DepName,");
            strSql.Append(" ParentId=@ParentId,");
            strSql.Append(" Status=@Status");
            strSql.Append(" WHERE Id=@Id");
            List<SqlParameter> pList = new List<SqlParameter>();
            pList.Add(new SqlParameter("@Id", SqlDbType.Int, 4) { Value = model.Id });
            pList.Add(new SqlParameter("@DepName", SqlDbType.NVarChar, 60) { Value = model.DepName });
            pList.Add(new SqlParameter("@ParentId", SqlDbType.Int, 4) { Value = model.ParentId });
            pList.Add(new SqlParameter("@Status", SqlDbType.TinyInt, 1) { Value = model.Status });
            return ExecuteNonQuery(ConfigHelper.ConnectionStrings, strSql.ToString(), pList.ToArray()) > 0;
        }

        public List<E_DepartmentInfo> GetDepartmentInfoList(string strWhere, int pageIndex, int pageSize, out int totalCount)
        {
            List<E_DepartmentInfo> list = new List<E_DepartmentInfo>();
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT * FROM (");
            strSql.Append("SELECT ROW_NUMBER() OVER(ORDER BY Id DESC) AS ORDERBYID");
            strSql.Append(",Id");
            strSql.Append(",DepName");
            strSql.Append(",ParentId");
            strSql.Append(",Status");
            strSql.AppendFormat(" FROM E_DepartmentInfo WITH(NOLOCK) WHERE 1=1 {0}) AS T WHERE T.ORDERBYID  BETWEEN {1} AND {2}", strWhere, pageIndex, pageSize);
            using (SqlDataReader r = ExecuteReader(ConfigHelper.ConnectionStrings, strSql.ToString()))
            {
                int Id = r.GetOrdinal("Id");
                int DepName = r.GetOrdinal("DepName");
                int ParentId = r.GetOrdinal("ParentId");
                int Status = r.GetOrdinal("Status");
                E_DepartmentInfo model = null;
                while (r.Read())
                {
                    model = new E_DepartmentInfo();
                    model.Id = r.GetInt32(Id);
                    model.DepName = r.GetString(DepName);
                    model.ParentId = r.GetInt32(ParentId);
                    model.Status = r.GetByte(Status);
                    list.Add(model);
                }
                r.Close();
            }
            totalCount = GetDepartmentInfoCount(strWhere);
            return list;
        }

        public List<E_DepartmentInfo> GetDepartmentInfoList(string strWhere)
        {
            List<E_DepartmentInfo> list = new List<E_DepartmentInfo>();
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT");
            strSql.Append(" Id");
            strSql.Append(",DepName");
            strSql.Append(",ParentId");
            strSql.Append(",Status");
            strSql.AppendFormat(" FROM E_DepartmentInfo WITH(NOLOCK) WHERE Status=0 {0}", strWhere);
            using (SqlDataReader r = ExecuteReader(ConfigHelper.ConnectionStrings, strSql.ToString()))
            {
                int Id = r.GetOrdinal("Id");
                int DepName = r.GetOrdinal("DepName");
                int ParentId = r.GetOrdinal("ParentId");
                int Status = r.GetOrdinal("Status");
                E_DepartmentInfo model = null;
                while (r.Read())
                {
                    model = new E_DepartmentInfo
                    {
                        Id = r.GetInt32(Id),
                        DepName = r.GetString(DepName),
                        ParentId = r.GetInt32(ParentId),
                        Status = r.GetByte(Status)
                    };
                    list.Add(model);
                }
                r.Close();
            }
            return list;
        }

        /// <summary>
        /// 根据Id剔除我下面所有的子级
        /// </summary>
        /// <param name="Id"></param>
        /// <returns></returns>
        public List<E_DepartmentInfo> GetDepartmentInfoList(int id)
        {
            List<E_DepartmentInfo> list = new List<E_DepartmentInfo>();
            string strSql = @"WITH    T AS ( SELECT   Id
               FROM     E_DepartmentInfo WITH ( NOLOCK )
               WHERE    Id = @Id
                        AND [Status] = 0
               UNION ALL
               SELECT   A.Id
               FROM     E_DepartmentInfo AS A WITH ( NOLOCK )
                        JOIN T AS B ON A.ParentId = B.Id
             )
    SELECT  Id ,
            DepName ,
            ParentId ,
            Status
    FROM    dbo.E_DepartmentInfo WITH ( NOLOCK )
    WHERE   [Status] = 0
            AND Id NOT IN ( SELECT  Id
                            FROM    T )";
            List<SqlParameter> pList = new List<SqlParameter>();
            pList.Add(new SqlParameter("@Id", SqlDbType.Int, 4) { Value = id });
            using (SqlDataReader r = ExecuteReader(ConfigHelper.ConnectionStrings, strSql, pList.ToArray()))
            {
                int Id = r.GetOrdinal("Id");
                int DepName = r.GetOrdinal("DepName");
                int ParentId = r.GetOrdinal("ParentId");
                int Status = r.GetOrdinal("Status");
                E_DepartmentInfo model = null;
                while (r.Read())
                {
                    model = new E_DepartmentInfo
                    {
                        Id = r.GetInt32(Id),
                        DepName = r.GetString(DepName),
                        ParentId = r.GetInt32(ParentId),
                        Status = r.GetByte(Status)
                    };
                    list.Add(model);
                }
                r.Close();
            }
            return list;
        }


        public int GetDepartmentInfoCount(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.AppendFormat("SELECT COUNT(*) FROM E_DepartmentInfo WITH(NOLOCK) WHERE 1=1 {0}", strWhere);
            return int.Parse(ExecuteScalar(ConfigHelper.ConnectionStrings, strSql.ToString()).ToString());
        }

        public E_DepartmentInfo GetDepartmentInfoModel(int id)
        {
            E_DepartmentInfo model = new E_DepartmentInfo();
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT ");
            strSql.Append("Id");
            strSql.Append(",DepName");
            strSql.Append(",ParentId");
            strSql.Append(",Status");
            strSql.Append(" FROM E_DepartmentInfo WITH(NOLOCK) WHERE Id=@Id");
            List<SqlParameter> pList = new List<SqlParameter>
            {
                new SqlParameter("@Id", SqlDbType.Int, 4) { Value = id }
            };
            using (SqlDataReader r = this.ExecuteReader(ConfigHelper.ConnectionStrings, strSql.ToString(), pList.ToArray()))
            {
                int Id = r.GetOrdinal("Id");
                int DepName = r.GetOrdinal("DepName");
                int ParentId = r.GetOrdinal("ParentId");
                int Status = r.GetOrdinal("Status");
                if (r.Read())
                {
                    model.Id = r.GetInt32(Id);
                    model.DepName = r.GetString(DepName);
                    model.ParentId = r.GetInt32(ParentId);
                    model.Status = r.GetByte(Status);
                }
                r.Close();
            }
            return model;
        }
 
    }
}
